import sqlite3
import datetime
import logging


class Database:
    def __init__(self, filename):
        self.conn = sqlite3.connect(filename)

    def is_user_exist(self, user_id):
        query = "SELECT ID FROM USERS WHERE USER_ID = {}".format(user_id)

        logging.log(logging.INFO, query)
        for _ in self.conn.execute(query):
            return True
        return False

    def save_word(self, user_id, words):
        query = "INSERT INTO USERS (USER_ID, WORDS, START_DATE, COUNTER) VALUES ({}, '{}', '{}', 0)".format(user_id,
                                                                                                            words,
                                                                                                            datetime.datetime.now())
        logging.log(logging.INFO, query)
        self.conn.execute(query)
        self.conn.commit()

    def delete_word_by_id(self, _id):
        query = "DELETE FROM USERS WHERE ID = {}".format(_id)

        logging.log(logging.INFO, query)
        self.conn.execute(query)
        self.conn.commit()

    def delete_word_from_user(self, user_id, word):
        query = "DELETE FROM USERS WHERE WORDS = '{}' AND USER_ID = {}".format(word, user_id)

        logging.log(logging.INFO, query)
        self.conn.execute(query)
        self.conn.commit()

    def update_word_counter(self, id, new_value):
        query = "UPDATE USERS SET COUNTER = {} WHERE ID = {}".format(new_value, id)

        logging.log(logging.INFO, query)
        self.conn.execute(query)

    def get_user(self, user_id):
        query = "SELECT ID, USER_ID, WORDS, START_DATE, COUNTER FROM USERS WHERE USER_ID = {}".format(user_id)

        logging.log(logging.INFO, query)
        return self.conn.execute(query)

    def get_all_users(self):
        query = "SELECT ID, USER_ID, WORDS, START_DATE, COUNTER FROM USERS"

        logging.log(logging.INFO, query)
        words = self.conn.execute(query)
        users = {}
        for i in words:
            if i[1] in users:
                users[i[1]].append(i)
            else:
                users[i[1]] = [i]
        return users

    def clear(self, user_id):
        query = "DELETE FROM USERS WHERE USER_ID = {}".format(user_id)

        logging.log(logging.INFO, query)
        self.conn.execute(query)


def format_message(words) -> str:
    return "Word list to remember:\n" + "\n".join(map(lambda m: " ".join(map(str, m)), words))
